package com.ssh.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class DBConnection {
	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;
	private static final String CLASSNAME;
	private static Connection conn = null;
	/**
	 * 加载驱动
	 */
	static {
		Properties prop = new Properties();
		try {
			prop.load(DBConnection.class.getResourceAsStream("/jdbc.properties"));
		} catch (IOException e1) {
			System.out.println("jdbc配置文件没找到");
		}
		URL = prop.getProperty("jdbc.url");
		USERNAME = prop.getProperty("jdbc.username");
		PASSWORD = prop.getProperty("jdbc.password");
		CLASSNAME = prop.getProperty("jdbc.classname");
		try {
			Class.forName(CLASSNAME);
			//System.out.println("加载驱动成功");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败，错误信息为：" + e.getMessage());
		}
	}
	/**
	 * 获取连接
	 * @return
	 */
	private static Connection getConnection() {
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			//System.out.println("连接成功");
		} catch (SQLException e) {
			System.out.println("获取连接失败，错误信息为：" + e.getMessage());
		}
		return conn;
	}
	/**
	 * 查询
	 * @param sql
	 * @return
	 */
	public static List<Map<String, Object>> query(String sql, Object...object){
		PreparedStatement prep = null;
		List<Map<String, Object>> list = new ArrayList<>();
		//System.out.println(sql);
		try {
			//获取语句对象
			prep = getConnection().prepareStatement(sql);
			ParameterMetaData pmd = prep.getParameterMetaData();
			if (null != object && object.length != 0 && pmd.getParameterCount() == object.length) {
				for (int i = 0, n = object.length; i < n; i++) {
					prep.setObject(i+1, object[i]);
				}
			}
			ResultSet rs = prep.executeQuery();
			//结果集结构化
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取数据库中字段的个数
			int columnCount = rsmd.getColumnCount();
			//获取字段名
			String[] columnName = new String[columnCount];
			for (int i = 0; i < columnCount; i++) {
				columnName[i] = rsmd.getColumnName(i+1);
			}
			//将每条记录加入到map中，然后将map加入到list
			while(rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 0; i < columnCount; i++) {
					String key = columnName[i];
					String value = rs.getString(key);
					map.put(key, value);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			System.out.println("大哥,可能你sql写错了!"+e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public static int update(String sql, Object...object) {
		PreparedStatement prep = null;
		int result = 0;
		//System.out.println(sql);
		try {
			//获取语句对象
			prep = getConnection().prepareStatement(sql);
			ParameterMetaData pmd = prep.getParameterMetaData();
			if (null != object && object.length != 0 && pmd.getParameterCount() == object.length) {
				for (int i = 0, n = object.length; i < n; i++) {
					prep.setObject(i+1, object[i]);
				}
			}
			result = prep.executeUpdate();
		}catch (SQLException e) {
			result = -1;
			System.out.println("大哥，可能你sql写错了！"+e.getMessage());
		}finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	} 
	/**
	    查询
	 * @param sql 语句
	 * @param clz list中的类型
	 * @param object 参数
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static <T> List<T> query(String sql, Class<T> clz, Object...object) {
		QueryRunner qr = new QueryRunner();
		List<T> list = null;
		try {
			list = (List<T>) qr.query(getConnection(), sql, object, new BeanListHandler<>(clz));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public static <T> T query(String sql,ResultSetHandler<T> rsh,Object...param) {
		T t=null;
		Connection connection=DBConnection.getConnection();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			ParameterMetaData pmd=psmt.getParameterMetaData();
			if(null!=param&&param.length!=0&pmd.getParameterCount()==param.length) {
				for(int i=0,len=param.length;i<len;i++) {
					psmt.setObject(i+1, param[i]);
				}
			}
			ResultSet rs=psmt.executeQuery();
			t=rsh.handler(rs);
		} catch (SQLException e) {
			System.out.println("update operation error,message is "+e.getMessage());
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return t;
	}
	
	public static void close() throws SQLException {
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
	}
	
}
